#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2020/12/5 13:43
# @Author  : Andrewq
# @Site    : nope
# @File    : OrderInfoModel.py
# @Software: PyCharm

from flask import current_app
from sqlalchemy import func
from .models import NewspaperInfo, DepartmentInfo, EmployeeInfo, OrderInfo
from .. import db
from ..utils import commons
from ..utils.response_code import RET


class OrderInfoManager(OrderInfo):
    @classmethod
    def get_order_all_info(cls):
        filter_list = []
        try:
            orders = db.session.query(cls.UserID,
                                      EmployeeInfo.UserName,
                                      cls.NewspaperID,
                                      NewspaperInfo.NewspaperName,
                                      cls.Number,
                                      cls.CreateTime).filter(*filter_list) \
                .join(NewspaperInfo, cls.NewspaperID == NewspaperInfo.NewspaperID) \
                .join(EmployeeInfo, EmployeeInfo.UserID == cls.UserID)

            if not orders.first():
                return {'code': RET.NODATA, 'message': '没有订阅数据', "data": []}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        return {'code': RET.OK, 'message': '查询所有数据成功', 'data': commons.query_to_dict(orders.all())}

    @classmethod
    def get_person_order_statistics(cls):

        filter_list = []
        filter_list.append(cls.NewspaperID == NewspaperInfo.NewspaperID)
        try:

            statistics = db.session.query(cls.NewspaperID,
                                          func.count(cls.UserID.distinct()).label('number')
                                          ).filter(*filter_list) \
                .group_by(cls.NewspaperID)

            if not statistics.first():
                return {'code': RET.NODATA, 'message': '没有部门的人员统计数据'}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        statistics_data = commons.query_to_dict(statistics.all())

        back_data = []

        try:
            for data in statistics_data:
                newspaper = db.session.query(NewspaperInfo).filter(
                    NewspaperInfo.NewspaperID == data.get('NewspaperID')).first()
                back_data.append({'index': newspaper.NewspaperName, 'number': data.get('number')})

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        return {'code': RET.OK, 'message': '统计部门人员的数据成功', 'data': back_data}

    @classmethod
    def get_department_order_statistics(cls):

        filter_list = []
        filter_list.append(cls.UserID == EmployeeInfo.UserID)
        filter_list.append(NewspaperInfo.NewspaperID == cls.NewspaperID)
        filter_list.append(EmployeeInfo.DepartmentID == DepartmentInfo.DepartmentID)
        try:

            statistics = db.session.query(NewspaperInfo.NewspaperID,
                                          func.count(EmployeeInfo.DepartmentID.distinct()).label('number')
                                          ).filter(*filter_list) \
                .group_by(NewspaperInfo.NewspaperID)

            if not statistics.first():
                return {'code': RET.NODATA, 'message': '没有部门的订阅统计数据'}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        statistics_data = commons.query_to_dict(statistics.all())

        back_data = []

        try:
            for data in statistics_data:
                newspaper = db.session.query(NewspaperInfo) \
                    .filter(NewspaperInfo.NewspaperID == data.get('NewspaperID')).first()
                back_data.append({'index': newspaper.NewspaperName, 'number': data.get('number')})

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        return {'code': RET.OK, 'message': '统计部门人员的数据成功', 'data': back_data}

    @classmethod
    def get_order_info_by_tags(cls, **kwargs):
        tag_name = kwargs.get('tag')
        keyword = kwargs.get('keyword')

        filter_list = []
        if tag_name == "用户ID":
            filter_list.append(cls.UserID == keyword)
        elif tag_name == "报纸ID":
            filter_list.append(cls.NewspaperID == keyword)
        elif tag_name == "报纸名":
            filter_list.append(NewspaperInfo.NewspaperName.like('%' + keyword + '%'))

        try:
            result_list = db.session.query(cls.UserID,
                                           EmployeeInfo.UserName,
                                           cls.NewspaperID,
                                           NewspaperInfo.NewspaperName,
                                           cls.Number,
                                           DepartmentInfo.DepartmentID,
                                           DepartmentInfo.DepartmentName,
                                           cls.CreateTime,
                                           ).filter(*filter_list) \
                .join(NewspaperInfo, cls.NewspaperID == NewspaperInfo.NewspaperID) \
                .join(EmployeeInfo, EmployeeInfo.UserID == cls.UserID) \
                .join(DepartmentInfo, DepartmentInfo.DepartmentID == EmployeeInfo.DepartmentID)
        except Exception as e:
            return {"code": RET.DBERR, "message": "查询出错", "error": str(e)}

        if not result_list.first():
            return {"code": RET.OK, "message": "暂无数据", "data": []}

        return {"code": RET.OK, "message": "查询成功！", "data": commons.query_to_dict(result_list.all())}

    @classmethod
    def add_department_order_info(cls, **kwargs):
        back_data_list = []
        datas = kwargs.get('datas')

        try:
            for data in datas:
                filter_list = []
                filter_list.append(EmployeeInfo.DepartmentID == data['data']['DepartmentID'])

                employees = db.session.query(EmployeeInfo).filter(*filter_list)

                if not employees.first():
                    continue

                for employee in commons.query_to_dict(employees.all()):
                    result_model = db.session.query(cls).filter(*[cls.UserID == employee['UserID'],
                                                                  cls.NewspaperID == data['data']['NewspaperID']])
                    if result_model.first():
                        result_model.update({"Number": result_model.first().Number + int(data["data"]["Number"])})
                        db.session.commit()
                        back_data_list.append(commons.query_to_dict(result_model.first()))
                    else:
                        Order = OrderInfo(
                            UserID=employee['UserID'],
                            NewspaperID=data['data']['NewspaperID'],
                            Number=data['data']['Number']
                        )
                        db.session.add(Order)
                        db.session.commit()
                        back_data_list.append(commons.query_to_dict(Order))

            if not back_data_list:
                return {'code': RET.NODATA, 'message': '无添加数据', 'data': []}

            return {'code': RET.OK, 'message': '添加部门订阅信息成功', 'data': back_data_list}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

    @classmethod
    def add_employee_order_info(cls, **kwargs):
        back_data_list = []
        datas = kwargs.get('datas')

        try:
            for data in datas:
                filter_list = []
                filter_list.append(EmployeeInfo.UserID == data['data']['UserID'])

                employees = db.session.query(EmployeeInfo).filter(*filter_list)

                if not employees.first():
                    continue

                for employee in commons.query_to_dict(employees.all()):
                    result_model = db.session.query(cls).filter(*[cls.UserID == employee['UserID'],
                                                                  cls.NewspaperID == data['data']['NewspaperID']])
                    if result_model.first():
                        result_model.update({"Number": result_model.first().Number + int(data["data"]["number"])})
                        db.session.commit()
                        back_data_list.append(commons.query_to_dict(result_model.first()))
                    else:
                        Order = OrderInfo(
                            UserID=employee['UserID'],
                            NewspaperID=data['data']['NewspaperID'],
                            Number=data['data']['number']
                        )
                        db.session.add(Order)
                        db.session.commit()
                        back_data_list.append(commons.query_to_dict(Order))

            return {'code': RET.OK, 'message': '添加部门订阅信息成功', 'data': back_data_list}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

    @classmethod
    def delete_order_info_list(cls, **kwargs):
        datas = kwargs.get('datas')
        delete_list = []
        try:
            for data in datas:
                filter_list = []
                filter_list.append(cls.UserID == data['data']['UserID'])
                filter_list.append(cls.CreateTime == data['data']['CreateTime'])
                filter_list.append(cls.NewspaperID == data['data']['NewspaperID'])
                filter_list.append(cls.Number == data['data']['Number'])

                order = db.session.query(cls).filter(*filter_list)

                if not order.first():
                    continue

                delete_list.append(commons.query_to_dict(order.first()))
                order.delete()
                db.session.commit()

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        return {'code': RET.OK, 'message': '成功删除数据', 'data': delete_list}

    @classmethod
    def get_order_department_statistics(cls):
        filter_list = []
        filter_list.append(DepartmentInfo.DepartmentID == EmployeeInfo.DepartmentID)
        filter_list.append(EmployeeInfo.UserID == OrderInfo.UserID)
        try:
            statistics = db.session.query(DepartmentInfo.DepartmentID,
                                          DepartmentInfo.DepartmentName,
                                          func.count(OrderInfo.NewspaperID.distinct()).label('number')
                                          ).filter(*filter_list) \
                .group_by(DepartmentInfo.DepartmentID, DepartmentInfo.DepartmentName)

            if not statistics.first():
                return {'code': RET.NODATA, 'message': '没有部门的订阅统计数据'}

        except Exception as e:
            current_app.logger.error(e)
            return {'code': RET.DBERR, 'message': '数据库异常，获取信息失败', 'error': str(e)}

        statistics_data = commons.query_to_dict(statistics.all())

        back_data = []

        for data in statistics_data:
            back_data.append({'index': data.get('DepartmentName'),
                              'number': data.get('number')})

        return {'code': RET.OK, 'message': '统计部门人员的数据成功', 'data': back_data}
